﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;
using System.Data;
using System.Data.SqlClient;

namespace WorkDAL.Meterials
{
    public class MaterialGroupDataAccess
    {
        ListDataContext listData = new ListDataContext();

        public static MaterialGroupDataAccess Instance()
        {
            return new MaterialGroupDataAccess();
        }

        public List<LiMaterialGroup> MaterialGroupGetList()
        {
            var liMaterialGroup = from c in this.listData.LiMaterialGroups
                                  orderby c.MortherGroupId, c.MaterialGroupId
                                 select c;

            return liMaterialGroup.ToList();
        }

        public LiMaterialGroup MaterialGroupGetById(string materialGroupId)
        {
            var liMaterialGroup = from c in this.listData.LiMaterialGroups
                                  where c.MaterialGroupId == materialGroupId
                                  select c;

            return (LiMaterialGroup)(liMaterialGroup.ToList()[0]);
        }

        public void CreateMaterialGroup(LiMaterialGroup item)
        {
            listData.LiMaterialGroups.InsertOnSubmit(item);
            listData.SubmitChanges();
        }

        public void UpdateMaterialGroup(LiMaterialGroup item)
        {
            LiMaterialGroup liMaterialGroup = listData.LiMaterialGroups.Single(p => p.MaterialGroupId == item.MaterialGroupId);
            liMaterialGroup.MaterialGroupName = item.MaterialGroupName;
            liMaterialGroup.MortherGroupId = item.MortherGroupId;
            liMaterialGroup.GroupEnd = item.GroupEnd;

            listData.SubmitChanges();
        }

        public void DeleteMaterialGroup(string materialGroupId)
        {
            LiMaterialGroup liMaterialGroup = listData.LiMaterialGroups.Single(p => p.MaterialGroupId == materialGroupId);
            
            listData.LiMaterialGroups.DeleteOnSubmit(liMaterialGroup);

            listData.SubmitChanges();
        }


        public List<MaterialGroupObject> MaterialGroupGetList(string materialGroupId)
        {
            List<MaterialGroupObject> materialObject = new List<MaterialGroupObject>();
            using (SqlConnection sqlConnection = new SqlConnection(CommonSettings.ConnectionString))
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand("MaterialGroupGetList", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add("@MaterialGroupId", SqlDbType.VarChar).Value = materialGroupId;

                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (sqlDataReader.Read())
                    {
                        if (sqlDataReader.HasRows)
                        {
                            MaterialGroupObject item = new MaterialGroupObject();

                            if (sqlDataReader["MaterialGroupId"] != DBNull.Value)
                            {
                                item.MaterialGroupId = sqlDataReader["MaterialGroupId"].ToString();
                            }

                            if (sqlDataReader["MaterialGroupName"] != DBNull.Value)
                            {
                                item.MaterialGroupName = sqlDataReader["MaterialGroupName"].ToString();
                            }


                            if (sqlDataReader["MortherGroupId"] != DBNull.Value)
                            {
                                item.MortherGroupId = sqlDataReader["MortherGroupId"].ToString();
                            }

                            if (sqlDataReader["GroupEnd"] != DBNull.Value)
                            {
                                item.GroupEnd = Convert.ToBoolean(sqlDataReader["GroupEnd"]);
                            }

                            if (sqlDataReader["RankGroup"] != DBNull.Value)
                            {
                                item.RankGroup = sqlDataReader["RankGroup"].ToString();
                            }

                            materialObject.Add(item);
                        }
                    }
                }


            }
            return materialObject;
        }

    }
}
